/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package xmlasm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.struts2.ServletActionContext;
import utils.DBUtils;
import xmlasm.action.RegisterAction;
import xmlasm.dto.User;
import xmlasm.dto.Users;

/**
 *
 * @author Hoang
 */
public class UserDAO {

    public static User checkLogin(String username, String password) {
        PreparedStatement stm = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = DBUtils.makeConnection();

            String sql = "SELECT * FROM [USER] WHERE  userName=? and password=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, username);
            stm.setString(2, password);
            rs = stm.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setFullName(rs.getString("fullName"));
                user.setUsername(username);
                user.setPassword(password);
                user.setEmail(rs.getString("email"));
                user.setIsAdmin(rs.getBoolean("isAdmin"));
                user.setIsActive(rs.getBoolean("isActive"));
                return user;
            }
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return null;
    }

    public static Users getAllUsers() {
        PreparedStatement stm = null;
        ResultSet rs = null;
        Connection con = null;
        Users users = new Users();
        try {
            con = DBUtils.makeConnection();
            String sql = "SELECT * FROM [USER]";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<User> temp = new ArrayList<User>();
            while (rs.next()) {
                User user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setFullName(rs.getString("fullName"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setIsAdmin(rs.getBoolean("isAdmin"));
                user.setIsActive(rs.getBoolean("isActive"));
                users.getUser().add(user);
            }
            return users;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return null;
    }

    public static int insertAccount(RegisterAction reg) {
        PreparedStatement stm = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = DBUtils.makeConnection();
            String sql = "INSERT INTO [USER](username, fullname, password, isAdmin, email, isActive)"
                    + " VALUES(?,?,?,0,?,1) SELECT @@IDENTITY AS userID";
            stm = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stm.setString(1, reg.getUsername());
            stm.setString(2, reg.getFullname());
            stm.setString(3, reg.getPassword());
            stm.setString(4, reg.getEmail());
            int result = stm.executeUpdate();
            if (result > 0) {
                rs = stm.getGeneratedKeys();
                if (rs.next()) {
                    int userId = rs.getInt("userID");
                    return userId;
                }
            }
            return -1;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    stm.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return -1;
    }

    public static boolean setActivation(int id, boolean status) {
        PreparedStatement stm = null;
        Connection con = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "UPDATE [USER] SET isActive=? WHERE userId=?";
            stm = con.prepareStatement(sql);
            stm.setBoolean(1, status);
            stm.setInt(2, id);
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }

    public static boolean setPermission(int id, boolean status) {
        PreparedStatement stm = null;
        Connection con = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "UPDATE [USER] SET isAdmin=? WHERE userId=?";
            stm = con.prepareStatement(sql);
            stm.setBoolean(1, status);
            stm.setInt(2, id);
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }

    public static boolean updateUser(User u) {
        PreparedStatement stm = null;
        Connection con = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "UPDATE [USER] SET fullName=?, password=?, email=? WHERE userId=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, u.getFullName());
            stm.setString(2, u.getPassword());
            stm.setString(3, u.getEmail());
            stm.setInt(4, u.getUserId());
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }
}
